package com.cgydu.dao.impl;

import com.cgydu.dao.UserDao;
import com.cgydu.entity.User;
import com.cgydu.utils.ConnectionUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;

/**
 * projectName: day25_store
 * author: CGY
 * time: 2020/8/26 11:32
 * description:
 */
public class UserDaoImpl implements UserDao {
    @Override
    public Long queryUserNameExist(String username) throws SQLException {

        String sql = "select count(1) as ct from user where u_name = ?;";

        QueryRunner queryRunner = new QueryRunner(ConnectionUtils.getDataSource());

        Long ct = (Long) queryRunner.query(sql, new ScalarHandler(),username);

        return ct;
    }

    @Override
    public int insertUser(User user) throws SQLException {

        String sql = "insert into user(u_name,u_password,u_email,u_sex,u_status,u_code,u_role) " +
                " value(?,?,?,?,?,?,?)";


        QueryRunner queryRunner = new QueryRunner(ConnectionUtils.getDataSource());

        int update = queryRunner.update(sql, user.getUsername(), user.getUpassword(), user.getEmail(),
                user.getUsex(), user.getUstatus(), user.getCode(),user.getUrole());

        return update;
    }

    @Override
    public int updateUserCode(String code, int activeCode) throws SQLException {

        String sql = "update user set u_status = ? where u_code = ?;";

        QueryRunner queryRunner = new QueryRunner(ConnectionUtils.getDataSource());

        int update = queryRunner.update(sql, activeCode,code);

        return update;
    }

    @Override
    public long queryUserByCode(String code) throws SQLException {

        String sql = "select count(1) from user where u_code = ?;";

        QueryRunner queryRunner = new QueryRunner(ConnectionUtils.getDataSource());

        Long count = (Long) queryRunner.query(sql, new ScalarHandler(), code);

        return count;
    }

    @Override
    public void updateUserCodeToNull(String code) throws SQLException {


        String sql = "update user set u_code = ? where u_code = ?;";

        QueryRunner queryRunner = new QueryRunner(ConnectionUtils.getDataSource());

        queryRunner.update(sql, "",code);

    }

    @Override
    public User queryUser(String username, String password) throws SQLException {

        String sql = "select u_id uid , u_name username,u_password upassword , u_email email " +
                ", u_status ustatus from user where u_name = ? and u_password = ?;";

        QueryRunner queryRunner = new QueryRunner(ConnectionUtils.getDataSource());

        User user = queryRunner.query(sql, new BeanHandler<User>(User.class), username, password);


        return user;
    }
}

